#! /bin/bash
HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]]; then
  TO_DATE=`date -d '1 days ago' "=%Y-%m-%d"`
else
 TO_DATE=$1
fi
${HIVE_HOME} -S -e "

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;

set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;

set hive.exec.orc.compression.strategy=COMPRESSION;


insert overwrite table zxoe_dwb.dwb_student_signin_record partition (dt)
select
    fs.class_id as class_id,
    student_id,
    signin_time,
    signin_date,
    share_state,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    class_date,
    content,
    studying_student_count,
    studying_date,
    fs.dt as dt
from zxoe_dwd.fact_tbh_student_signin_record fs
left join zxoe_dwd.dim_course_table_upload_detail dc
on fs.class_id=dc.class_id and fs.signin_date=dc.class_date
left join zxoe_dwd.dim_tbh_class_time_table dts
on fs.time_table_id=dts.id
left join zxoe_dwd.dim_class_studying_student_count dsc
on dc.class_id=dsc.class_id and dc.class_date=dsc.studying_date
where cast(dc.class_date as timestamp) between cast(dts.use_begin_date as timestamp) and cast(dts.use_end_date as timestamp)
and cast(fs.dt as date)=${TO_DATE}
order by class_date DESC
;

with dim_temp as (
select
    dc.class_id,
    class_date,
    content,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    studying_student_count,
    studying_date,
    dc.dt as dt
from zxoe_dwd.dim_course_table_upload_detail dc
left join zxoe_dwd.dim_tbh_class_time_table dts
on dc.class_id=dts.class_id
left join zxoe_dwd.dim_class_studying_student_count dsc
on dsc.class_id=dc.class_id and dsc.studying_date=dc.class_date
where cast(dc.class_date as timestamp) between cast(dts.use_begin_date as timestamp) and cast(dts.use_end_date as timestamp)
and cast(dc.dt as date) = ${TO_DATE}
),
morning as(
select
    dts.class_id as class_id,
    student_id,
    audit_state,
    begin_time,
    end_time,
    create_time,
    class_date,
    content,
    morning_begin_time,
    morning_end_time,
    studying_student_count,
    studying_date,
    dts.dt as dt
from dim_temp dts
left join zxoe_dwd.fact_student_leave_apply fl
on dts.class_id =fl.class_id
where
cast(concat_ws(' ',dts.studying_date,dts.morning_begin_time) as timestamp) between cast(fl.begin_time as timestamp) and cast(fl.end_time as timestamp)
or cast(concat_ws(' ',dts.studying_date,dts.morning_end_time) as timestamp) between cast(fl.begin_time as timestamp) and cast(fl.end_time as timestamp)),
afternoon as(
select
    dts.class_id as class_id,
    student_id,
    audit_state,
    begin_time,
    end_time,
    create_time,
    class_date,
    content,
    afternoon_begin_time,
    afternoon_end_time,
    studying_student_count,
    studying_date,
    dts.dt as dt
from dim_temp dts
left join zxoe_dwd.fact_student_leave_apply fl
on dts.class_id =fl.class_id
where
cast(concat_ws(' ',dts.studying_date,dts.afternoon_begin_time) as timestamp) between cast(fl.begin_time as timestamp) and cast(fl.end_time as timestamp)
or cast(concat_ws(' ',dts.studying_date,dts.afternoon_end_time) as timestamp) between cast(fl.begin_time as timestamp) and cast(fl.end_time as timestamp)
),
evening as(
select
    dts.class_id as class_id,
    student_id,
    audit_state,
    begin_time,
    end_time,
    create_time,
    class_date,
    content,
    evening_begin_time,
    evening_end_time,
    studying_student_count,
    studying_date,
    dts.dt as dt
from dim_temp dts
left join zxoe_dwd.fact_student_leave_apply fl
on dts.class_id =fl.class_id
where
cast(concat_ws(' ',dts.studying_date,dts.evening_begin_time) as timestamp) between cast(fl.begin_time as timestamp) and cast(fl.end_time as timestamp)
or cast(concat_ws(' ',dts.studying_date,dts.evening_end_time) as timestamp) between cast(fl.begin_time as timestamp) and cast(fl.end_time as timestamp)
)
insert overwrite table zxoe_dwb.dwb_student_leave partition (dt)
select
    m.class_id,
    m.student_id,
    m.audit_state,
    m.begin_time,
    m.end_time,
    m.create_time,
    m.class_date,
    m.content,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    m.studying_student_count,
    m.studying_date,
    m.dt
from morning m
full join afternoon a
on m.student_id=a.student_id and m.create_time=a.create_time
full join evening e
on m.student_id=e.student_id and m.create_time=e.create_time;

"